package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.beans.Users;

@Repository (value="userdao")
public class UserDAOImpl implements UserDAO{
	private JdbcTemplate jt;
	@Autowired
	public UserDAOImpl(DataSource data)
	{
		this.jt=new JdbcTemplate(data);
	}

	@Override
	public int add(final Users user) {
		// TODO Auto-generated method stub
		int id=0;
		if(user!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_ADD(?,?,?,?,?,?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, user.getUserName());
							stm.setString(2, user.getPassWord());
							stm.setString(3, user.getLastLogIP());
							stm.setString(4, user.getDescription());
							stm.setString(5, user.getEmail());
							stm.setBytes(6, user.getHeadImage());
							stm.setString(7, user.getCountry());
							stm.setString(8, user.getAddress());
							stm.setString(9, user.getTel());
							stm.setString(10, user.getBirthday());
							stm.setString(11, user.getNickName());
							stm.setString(12, user.getUserRole());
							stm.registerOutParameter(13, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(13);
								obj[1]=rs;
								return obj;
							}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delete(final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_DELETE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}
	
	@Override
	public boolean edit(final Users user, final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if((user!=null)&&(id>0))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_EDIT(?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.setString(2, user.getUserName());
							stm.setString(3, user.getPassWord());
							stm.setString(4, user.getLastLogIP());
							stm.setString(5, user.getDescription());
							stm.setString(6, user.getEmail());
							stm.setBytes(7, user.getHeadImage());
							stm.setString(8, user.getCountry());
							stm.setString(9, user.getAddress());
							stm.setString(10, user.getTel());
							stm.setString(11, user.getBirthday());
							stm.setString(12, user.getNickName());
							stm.setString(13, user.getUserRole());
							stm.registerOutParameter(14, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(14);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public Users get(final int id) {
		// TODO Auto-generated method stub
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GET(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=null;
								Users user =new Users();
								if (rs.next())
								{
									user.setAddress(rs.getString("address"));
									user.setBirthday(rs.getString("birthday"));
									user.setCountry(rs.getString("country"));
									user.setDescription(rs.getString("description"));
									user.setEmail(rs.getString("email"));
									user.setHeadImage(rs.getBytes("headimage"));
									user.setId(rs.getInt("id"));
									user.setLastLogIn(rs.getTimestamp("lastlogin"));
									user.setLastLogIP(rs.getString("lastlogip"));
									user.setNickName(rs.getString("nickname"));
									user.setPassWord(rs.getString("password"));
									user.setRegisterd(rs.getTimestamp("registerd"));
									user.setTel(rs.getString("tel"));
									user.setUserName(rs.getString("username"));
									user.setUserRole(rs.getString("userrole"));
								}
								obj[1]=user;
								return obj;
							}});
			if(((Boolean) obj[0]).booleanValue())
				return (Users) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Users> getAll() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL USER_GETALL(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Users> users=new ArrayList<Users>();
							while (rs.next())
							{
								Users user =new Users();
								user.setAddress(rs.getString("address"));
								user.setBirthday(rs.getString("birthday"));
								user.setCountry(rs.getString("country"));
								user.setDescription(rs.getString("description"));
								user.setEmail(rs.getString("email"));
								user.setHeadImage(rs.getBytes("headimage"));
								user.setId(rs.getInt("id"));
								user.setLastLogIn(rs.getTimestamp("lastlogin"));
								user.setLastLogIP(rs.getString("lastlogip"));
								user.setNickName(rs.getString("nickname"));
								user.setPassWord(rs.getString("password"));
								user.setRegisterd(rs.getTimestamp("registerd"));
								user.setTel(rs.getString("tel"));
								user.setUserName(rs.getString("username"));
								user.setUserRole(rs.getString("userrole"));
								users.add(user);
							}
							obj[1]=users;
							return obj;
						}});
		return (List<Users>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Users> getLastJoined() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL USER_GETLASTJOINED(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Users> users=new ArrayList<Users>();
							while (rs.next())
							{
								Users user =new Users();
								user.setAddress(rs.getString("address"));
								user.setBirthday(rs.getString("birthday"));
								user.setCountry(rs.getString("country"));
								user.setDescription(rs.getString("description"));
								user.setEmail(rs.getString("email"));
								user.setHeadImage(rs.getBytes("headimage"));
								user.setId(rs.getInt("id"));
								user.setLastLogIn(rs.getTimestamp("lastlogin"));
								user.setLastLogIP(rs.getString("lastlogip"));
								user.setNickName(rs.getString("nickname"));
								user.setPassWord(rs.getString("password"));
								user.setRegisterd(rs.getTimestamp("registerd"));
								user.setTel(rs.getString("tel"));
								user.setUserName(rs.getString("username"));
								user.setUserRole(rs.getString("userrole"));
								users.add(user);
							}
							obj[1]=users;
							return obj;
						}});
		return (List<Users>) obj[1];
	}

	@Override
	public int verify(final String uname) {
		// TODO Auto-generated method stub
		if(uname!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_VERIFY(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, uname);
							stm.registerOutParameter(2, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Integer) obj[0]).intValue();
		}
		return 3;
	}

	@Override
	public boolean login(final int userid,final String logip) {
		// TODO Auto-generated method stub
		if((userid>0)&&(logip!=null))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_LOGIN(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, userid);
							stm.setString(2, logip);
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(3);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public Users getByName(final String uname) {
		// TODO Auto-generated method stub
		if(uname!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GETBYNAME(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, uname);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=null;
								Users user =new Users();
								if (rs.next())
								{
									user.setAddress(rs.getString("address"));
									user.setBirthday(rs.getString("birthday"));
									user.setCountry(rs.getString("country"));
									user.setDescription(rs.getString("description"));
									user.setEmail(rs.getString("email"));
									user.setHeadImage(rs.getBytes("headimage"));
									user.setId(rs.getInt("id"));
									user.setLastLogIn(rs.getTimestamp("lastlogin"));
									user.setLastLogIP(rs.getString("lastlogip"));
									user.setNickName(rs.getString("nickname"));
									user.setPassWord(rs.getString("password"));
									user.setRegisterd(rs.getTimestamp("registerd"));
									user.setTel(rs.getString("tel"));
									user.setUserName(rs.getString("username"));
									user.setUserRole(rs.getString("userrole"));
								}
								obj[1]=user;
								return obj;
							}});
			if(((Boolean) obj[0]).booleanValue())
				return (Users) obj[1];
		}
		return null;
	}

	@Override
	public byte[] getHeadImage(final int userid) {
		// TODO Auto-generated method stub
		if(userid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GETHEADIMAGE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, userid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("headimage");
								}
								return obj;
							}});
			if(((Boolean) obj[0]).booleanValue())
				return (byte[]) obj[1];
		}
		return null;
	}

}
